﻿Imports System.Data.OleDb
Public Class Customer_Master
    Dim cmd As OleDbCommand
    Dim dr As OleDbDataReader
    Private Sub Customer_Master_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            filllist()
            disableall()
            fillgrid()
            loadwork()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Private Sub loadwork()
        Dim str As String
        str = "select count(*) from Customer_master"
        cmd = New OleDbCommand(str, con)
        If cmd.ExecuteScalar > 3 Then
            MsgBox("You demo limit for customer is over")
            btnedit.Enabled = False
        End If
    End Sub
    Private Sub filllist()
        Dim str As String
        str = "select distinct(customer_name) from customer_master order by customer_name"
        cmd = New OleDbCommand(str, con)
        dr = cmd.ExecuteReader
        While dr.Read
            cmbname.Items.Add(UCase(dr(0).ToString))
        End While
    End Sub
    Private Sub fillgrid()
        Dim str As String, i As Integer
        DataGridView1.Rows.Clear()
        str = "select * from customer_master order by customer_name"
        cmd = New OleDbCommand(str, con)
        dr = cmd.ExecuteReader
        While dr.Read
            DataGridView1.Rows.Add()
        End While
        cmd = New OleDbCommand(str, con)
        dr = cmd.ExecuteReader
        While dr.Read
            DataGridView1.Item(0, i).Value = dr(0).ToString
            DataGridView1.Item(1, i).Value = dr(2).ToString
            DataGridView1.Item(2, i).Value = dr(3).ToString
            DataGridView1.Item(3, i).Value = dr(4).ToString
            DataGridView1.Item(4, i).Value = dr(5).ToString
            DataGridView1.Item(5, i).Value = dr(6).ToString
            i = i + 1
        End While
    End Sub
    Private Sub cmbname_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles cmbname.KeyDown
        Try
            Dim str As String
            If e.KeyCode = Keys.Enter Then
                enableall()
                btnclose.Text = "Cancel"
                dtafter.Enabled = False
                If cmbname.Items.Contains(UCase(cmbname.Text)) = True Then
                    str = "select customer_ID,customer_address,customer_phone_no,customer_op_balance,customer_other,cdate from (select customer_ID,customer_name,customer_address,customer_phone_no,customer_op_balance,customer_other,cdate from customer_master LEFT join consolidate ON customer_master.customer_id=consolidate.accountid) where  customer_name='" & cmbname.Text & "'"
                    cmd = New OleDbCommand(str, con)
                    txtaddress.Text = str
                    dr = cmd.ExecuteReader
                    If dr.Read Then
                        lblid.Text = dr(0).ToString
                        txtaddress.Text = dr(1).ToString
                        txtphone.Text = dr(2).ToString
                        txtopening.Text = dr(3).ToString
                        txtother.Text = dr(4).ToString
                        If dr(5).ToString <> "" Then
                            dtafter.Value = Format(dr(5), "dd/MMM/yyyy")
                            dtafter.Checked = True
                        Else
                            dtafter.Value = Today.Date
                            dtafter.Checked = False
                        End If
                        dtafter.Enabled = True
                    End If
                    txtaddress.Focus()
                Else
                    txtaddress.Text = ""
                    txtopening.Text = ""
                    txtother.Text = ""
                    txtphone.Text = ""
                    lblid.Text = ""
                    btnclose.Text = "Cancel"
                    lblid.Text = ""
                    txtaddress.Focus()
                    If dtafter.Checked = False Then

                        dtafter.Enabled = False
                    End If
                End If
            End If
        Catch ex As Exception
            Exit Sub
        End Try
    End Sub

    Private Sub btnclose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclose.Click
        If btnclose.Text = "Close" Then
            Me.Close()
        ElseIf btnclose.Text = "Cancel" Then
            disableall()
            txtaddress.Text = ""
            txtopening.Text = ""
            txtother.Text = ""
            txtphone.Text = ""
            cmbname.Text = ""
            lblid.Text = ""
            btnclose.Text = "Close"
        End If

    End Sub

    Private Sub btnedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnedit.Click
        Try
            Dim str As String
            If lblid.Text = "" Then
                str = "select count(*) from customer_master where customer_name='" & cmbname.Text & "'"
                cmd = New OleDbCommand(str, con)
                If cmd.ExecuteScalar > 0 Then
                    MsgBox("Duplicate entry exist")
                    Exit Sub
                End If
                str = "insert into customer_master(Customer_name,customer_address,customer_phone_no,customer_op_balance,customer_other) values('" & cmbname.Text & "','" & txtaddress.Text & "','" & txtphone.Text & "'," & Val(txtopening.Text) & ",'" & txtother.Text & "')"
            Else
                str = "update customer_master set customer_name='" & cmbname.Text & "',customer_address='" & txtaddress.Text & "',customer_phone_no='" & txtphone.Text & "',customer_op_balance=" & Val(txtopening.Text) & ",customer_other='" & txtother.Text & "' where customer_id=" & Val(lblid.Text)
            End If
            cmd = New OleDbCommand(str, con)
            If cmd.ExecuteNonQuery > 0 Then
            Else
                MsgBox("Error while updation")
                Exit Sub
            End If
            If dtafter.Checked = False Then
                If lblid.Text <> "" Then
                    str = "delete from consolidate where accountid=" & Val(lblid.Text)
                    cmd = New OleDbCommand(str, con)
                    cmd.ExecuteNonQuery()
                End If
            Else
                If lblid.Text <> "" Then
                    str = "update consolidate set cdate=#" & Format(dtafter.Value, "dd/MMM/yyyy") & " where accountid=" & Val(lblid.Text)
                    cmd = New OleDbCommand(str, con)
                    cmd.ExecuteNonQuery()
                End If
            End If
            txtaddress.Text = ""
            txtopening.Text = ""
            txtother.Text = ""
            txtphone.Text = ""
            cmbname.Text = ""
            lblid.Text = ""
            disableall()
            fillgrid()
        Catch ex As Exception

        End Try
    End Sub
    Private Sub disableall()
        txtaddress.Enabled = False
        txtopening.Enabled = False
        txtother.Enabled = False
        txtphone.Enabled = False
        dtafter.Enabled = False
        btnedit.Enabled = False
    End Sub
    Private Sub enableall()
        txtaddress.Enabled = True
        txtopening.Enabled = True
        txtother.Enabled = True
        txtphone.Enabled = True
        dtafter.Enabled = True
        btnedit.Enabled = True
    End Sub

    Private Sub txtopening_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtopening.KeyPress
        If Asc(e.KeyChar) = 46 Then
            If sender.text.contains(e.KeyChar) = True Then
                e.KeyChar = ""
            Else
                Exit Sub
            End If
        End If
        If IsNumeric(e.KeyChar) = False And Asc(e.KeyChar) <> 8 And Asc(e.KeyChar) <> 46 Then
            e.KeyChar = ""
        End If
    End Sub
    Private Sub DataGridView1_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellDoubleClick
        Try
            Dim str As String
            disableall()
            lblid.Text = DataGridView1.Item(0, e.RowIndex).Value
            cmbname.Text = DataGridView1.Item(1, e.RowIndex).Value
            txtaddress.Text = DataGridView1.Item(2, e.RowIndex).Value
            txtphone.Text = DataGridView1.Item(3, e.RowIndex).Value
            txtopening.Text = DataGridView1.Item(4, e.RowIndex).Value
            txtother.Text = DataGridView1.Item(5, e.RowIndex).Value
            dtafter.Enabled = False
            str = "select cdate from consolidate where accountid=" & Val(lblid.Text)
            cmd = New OleDbCommand(str, con)
            dr = cmd.ExecuteReader
            enableall()
            If dr.Read Then
                dtafter.Enabled = True
                dtafter.Value = Format(dr(0), "dd/MMM/yyyy")
            Else
                dtafter.Enabled = False
            End If
        Catch ex As Exception

        End Try
    End Sub


    Private Sub DataGridView1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DataGridView1.KeyDown
        If e.KeyCode = Keys.Delete Then
            If DataGridView1.Rows.Count = 0 Then
                Exit Sub
            End If
            Dim str As String
            If MessageBox.Show("Are you sure you want to delete this Party", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
                str = "delete from customer_master where customer_id=" & Val(DataGridView1.Item(0, DataGridView1.CurrentRow.Index).Value)
                cmd = New OleDbCommand(str, con)
                cmd.ExecuteNonQuery()
                fillgrid()
            Else
            End If

        End If
    End Sub

    Private Sub DataGridView1_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DataGridView1.KeyUp
        fillgrid()
    End Sub
End Class